﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using iShoppingDTO;

namespace iShoppingDAO
{
    public class TaiKhoanDAO
    {
        //Hue
        public static DataTable LichSuDauGiaCuaTaiKhoanDAO(string _tenTaiKhoan)
        {
            DataTable resTable = new DataTable();
            try
            {

                //string sql = "SELECT * FROM TaiKhoan tk, LoaiTaiKhoan l WHERE tk.MaLoaiTK = l.MaLoaiTK AND tk.MaTaiKhoan = @MaTaiKhoan";
                string sql = "select tk.TenTaiKhoan as 'Tên Tài Khoản',ctgd.ThoiGianGiaoDich as 'Thời Gian Giao Dịch', sp.MaSanPham as 'Mã Sản Phẩm' , sp.TenSanPham as'Tên Sản Phẩm',ctgd.GiaGiaoDich as 'Giá Giao Dịch',ctgd.TinhTrang as 'Tình Trạng' from TAIKHOAN tk, CHITIETGIAODICH ctgd, SANPHAM sp where tk.MaTaiKhoan = ctgd.MaTaiKhoan and sp.MaSanPham = ctgd.MaSanPham and tk.TenTaiKhoan = @TenTaiKhoan order by ctgd.ThoiGianGiaoDich desc";
                List<SqlParameter> sqlParameters = new List<SqlParameter>();

                SqlParameter[] par = new SqlParameter[2];
                par[0] = new SqlParameter("@TenTaiKhoan", SqlDbType.NVarChar);
                par[0].Value = _tenTaiKhoan;
                sqlParameters.Add(par[0]);

                resTable = DataProvider.ExecuteQuery(sql, sqlParameters);
            }
            catch (Exception ex)
            {
                resTable = new DataTable();
            }
            return resTable;

        }
        //Hue
        public bool Khoa_MoKhoaTaiKhoanDAO(string tenTK, string tinhTrang)
        {
            bool res = false;
            try
            {

                string sql = "UPDATE TaiKhoan SET TrangThai = @TrangThai WHERE TenTaiKhoan = @TenTaiKhoan";

                List<SqlParameter> sqlParameters = new List<SqlParameter>();

                SqlParameter[] par = new SqlParameter[2];
                par[0] = new SqlParameter("@TrangThai", SqlDbType.NVarChar);
                par[0].Value = tinhTrang;
                sqlParameters.Add(par[0]);

                par[1] = new SqlParameter("@TenTaiKhoan", SqlDbType.NVarChar);
                par[1].Value = tenTK;
                sqlParameters.Add(par[1]);

                res = DataProvider.ExecuteNoneQuery(sql, sqlParameters);

            }
            catch (Exception ex)
            {
                res = false;
            }
            return res;
        }

        public static string GetTaiKhoanOfTens(string tenTaiKhoan)
        {
            string tk = null;
            try
            {

                string sql = "SELECT * FROM TaiKhoan WHERE TenTaiKhoan = '" + tenTaiKhoan + "'";
                DataTable dt = DataProvider.ExecuteQuery(sql);

                int i, n = dt.Rows.Count;
                for (i = 0; i < n; i++)
                {
                   
                    tk = dt.Rows[i]["MaTaiKhoan"].ToString();
                  

                }

            }
            catch (Exception ex)
            {
                tk = null;
            }
            return tk;
        }

        public static TaiKhoanDTO GetTaiKhoanOfTen(string tenTaiKhoan)
        {
            TaiKhoanDTO tk = null;
            try
            {

                string sql = "SELECT * FROM TaiKhoan WHERE TenTaiKhoan = '" + tenTaiKhoan + "'";
                DataTable dt = DataProvider.ExecuteQuery(sql);

                int i, n = dt.Rows.Count;
                for (i = 0; i < n; i++)
                {
                    tk = new TaiKhoanDTO();
                    tk.MaTaiKhoan = dt.Rows[i]["MaTaiKhoan"].ToString();
                    tk.TenTaiKhoan = dt.Rows[i]["TenTaiKhoan"].ToString();
                    tk.MatKhau = dt.Rows[i]["MatKhau"].ToString();
                    tk.MaLoaiTK = dt.Rows[i]["MaLoaiTK"].ToString().Trim();
                    tk.Avatar = dt.Rows[i]["Avatar"].ToString();
                    tk.TrangThai = dt.Rows[i]["TrangThai"].ToString();
                    tk.DiemTinCayBan = dt.Rows[i]["DiemTinCayBan"].ToString();
                    tk.DiemTinCayMua = dt.Rows[i]["DiemTinCayMua"].ToString();

                }

            }
            catch (Exception ex)
            {
                tk = new TaiKhoanDTO();
            }
            return tk;
        }


        public static TaiKhoanDTO GetTaiKhoanOfMa(string maTK)
        {
            TaiKhoanDTO tk = null;
            try
            {

                string sql = "SELECT * FROM TaiKhoan WHERE MaTaiKhoan = '" + maTK + "'";
                DataTable dt = DataProvider.ExecuteQuery(sql);

                int i, n = dt.Rows.Count;
                for (i = 0; i < n; i++)
                {
                    tk = new TaiKhoanDTO();
                    tk.MaTaiKhoan = dt.Rows[i]["MaTaiKhoan"].ToString();
                    tk.TenTaiKhoan = dt.Rows[i]["TenTaiKhoan"].ToString();
                    tk.MatKhau = dt.Rows[i]["MatKhau"].ToString();
                    tk.MaLoaiTK = dt.Rows[i]["MaLoaiTK"].ToString().Trim();
                    tk.Avatar = dt.Rows[i]["Avatar"].ToString();
                    tk.TrangThai = dt.Rows[i]["TrangThai"].ToString();
                    tk.DiemTinCayBan = dt.Rows[i]["DiemTinCayBan"].ToString();
                    tk.DiemTinCayMua = dt.Rows[i]["DiemTinCayMua"].ToString();

                }

            }
            catch (Exception ex)
            {
                tk = new TaiKhoanDTO();
            }
            return tk;
        }

        public static bool insertTaiKhoan(TaiKhoanDTO tk) {
            try
            {
                string sql = string.Format("INSERT INTO TaiKhoan(MaTaiKhoan, TenTaiKhoan, MatKhau, MaLoaiTK, DiemTinCayBan, DiemTinCayMua) VALUES('{0}', '{1}', '{2}', 2, '50', '50')",
                    tk.MaTaiKhoan, tk.TenTaiKhoan, tk.MatKhau);
                bool result = DataProvider.ExecuteNoneQuery(sql);
                return result;
            }
            catch (Exception e)
            {

            }
            return false;
        }

        public static bool checkTenTaiKhoan(string tenTaiKhoan) {
            try {
                string sql = string.Format("SELECT * FROM TaiKhoan WHERE TenTaiKhoan = '{0}'", tenTaiKhoan);
                DataTable dt = DataProvider.ExecuteQuery(sql);

                int n = dt.Rows.Count;
                if (n != 0)
                    return true;
            }
            catch(Exception e) {
                
            }
            return false;
        }

        public static bool checkTaiKhoan(TaiKhoanDTO tk) {
            try {
                string sql = string.Format("SELECT * FROM TaiKhoan WHERE TenTaiKhoan = '{0}' AND MatKhau = '{1}'", tk.TenTaiKhoan, tk.MatKhau);
                DataTable dt = DataProvider.ExecuteQuery(sql);

                int n = dt.Rows.Count;
                if (n != 0)
                    return true;
            }
            catch(Exception e) {
                
            }
            return false;
        }

        public DataTable getTaiKhoanOfMas(string maTK)
        {
            DataTable dt = new DataTable();
            try
            {

                string sql = "SELECT * FROM TaiKhoan tk, ThongTinTK tt WHERE  tk.MaTaiKhoan = tt.MaTaiKhoan AND tk.MaTaiKhoan = '" + maTK + "'";
                dt = DataProvider.ExecuteQuery(sql);


            }
            catch (Exception ex)
            {
                dt = new DataTable();
            }
            return dt;
        }


        //dung
        public static string LayMaLoaiTK(string tenTaiKhoan)
        {
            string maloaitk = null;
            try
            {

                string sql = "SELECT MaLoaiTK FROM TaiKhoan WHERE TenTaiKhoan = '" + tenTaiKhoan + "'";
                DataTable dt = DataProvider.ExecuteQuery(sql);
                int i, n = dt.Rows.Count;
                for (i = 0; i < n; i++)
                {

                    maloaitk = dt.Rows[i]["MaLoaiTK"].ToString();


                }

            }
            catch (Exception ex)
            {
                maloaitk = null;
            }
            return maloaitk;
        }
        //end of dung

        public DataTable LoaiTaiKhoan(string maTaiKkhoan)
        {
            DataTable resTable = new DataTable();
            try
            {

                string sql = "SELECT * FROM TaiKhoan tk, LoaiTaiKhoan l WHERE tk.MaLoaiTK = l.MaLoaiTK AND tk.MaTaiKhoan = @MaTaiKhoan";

                List<SqlParameter> sqlParameters = new List<SqlParameter>();

                SqlParameter[] par = new SqlParameter[2];
                par[0] = new SqlParameter("@MaTaiKhoan", SqlDbType.NVarChar);
                par[0].Value = maTaiKkhoan;
                sqlParameters.Add(par[0]);

                resTable = DataProvider.ExecuteQuery(sql, sqlParameters); 
                

            }
            catch (Exception ex)
            {
                resTable = new DataTable();
            }
            return resTable;

        }

       
        public bool UpdateTaiKhoanOfAvatar(TaiKhoanDTO taiKhoanDto)
        {
            bool res = false;
            try
            {

                string sql = "UPDATE TaiKhoan SET Avatar = @Avatar WHERE MaTaiKhoan = @MaTaiKhoan";

                List<SqlParameter> sqlParameters = new List<SqlParameter>();

                SqlParameter[] par = new SqlParameter[2];
                par[0] = new SqlParameter("@Avatar", SqlDbType.NVarChar);
                par[0].Value = taiKhoanDto.Avatar;
                sqlParameters.Add(par[0]);

                par[1] = new SqlParameter("@MaTaiKhoan", SqlDbType.Int);
                par[1].Value = taiKhoanDto.MaTaiKhoan;
                sqlParameters.Add(par[1]);

                res = DataProvider.ExecuteNoneQuery(sql, sqlParameters);

            }
            catch (Exception ex)
            {
                res = false;
            }
            return res;
        }

        public bool UpdateTaiKhoanOfMatKhau(string maTK, string matKhau)
        {
            bool res = false;
            try
            {

                string sql = "UPDATE TaiKhoan SET MatKhau = @MatKhau WHERE MaTaiKhoan = @MaTaiKhoan";

                List<SqlParameter> sqlParameters = new List<SqlParameter>();

                SqlParameter[] par = new SqlParameter[2];
                par[0] = new SqlParameter("@MatKhau", SqlDbType.NVarChar);
                par[0].Value = matKhau;
                sqlParameters.Add(par[0]);

                par[1] = new SqlParameter("@MaTaiKhoan", SqlDbType.NVarChar);
                par[1].Value = maTK;
                sqlParameters.Add(par[1]);

                res = DataProvider.ExecuteNoneQuery(sql, sqlParameters);

            }
            catch (Exception ex)
            {
                res = false;
            }
            return res;
        }

        public static DataTable SelectAllTaiKhoan()
        {
            DataTable dt = new DataTable();
            try
            {

                string sql = "SELECT * FROM TaiKhoan tk, ThongTinTK tt, LoaiTaiKhoan ltk WHERE  tk.MaTaiKhoan = tt.MaTaiKhoan AND tk.MaLoaiTK = ltk.MaLoaiTK";
                dt = DataProvider.ExecuteQuery(sql);


            }
            catch (Exception ex)
            {
                dt = new DataTable();
            }
            return dt;
        }

        public static bool InsertAllTaiKhoan(TaiKhoanDTO tk)
        {
            try
            {
                string sql = string.Format("INSERT INTO TaiKhoan(MaTaiKhoan, TenTaiKhoan, MatKhau, MaLoaiTK, Avatar, TrangThai, DiemTinCayBan, DiemTinCayMua) VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '{5}','{6}','{7}')",
                    tk.MaTaiKhoan, tk.TenTaiKhoan, tk.MatKhau, tk.MaLoaiTK, tk.Avatar, tk.TrangThai, tk.DiemTinCayBan, tk.DiemTinCayMua);
                bool result = DataProvider.ExecuteNoneQuery(sql);
                return result;
            }
            catch (Exception e)
            {

            }
            return false;
        }

        public static bool UpdateTaiKhoan(TaiKhoanDTO taiKhoanDto)
        {
            bool res = false;
            try
            {
                List<SqlParameter> sqlParameters = new List<SqlParameter>();
                string sql = "UPDATE TaiKhoan SET TenTaiKhoan = @TenTaiKhoan , MaLoaiTK = @MaLoaiTK";
                sql += " , TrangThai = @TrangThai , DiemTinCayBan = @DiemTinCayBan , DiemTinCayMua = @DiemTinCayMua WHERE MaTaiKhoan = @MaTaiKhoan";
                SqlParameter[] parameter = new SqlParameter[6];
              
                parameter[0] = new SqlParameter("@TenTaiKhoan", SqlDbType.NVarChar);
                parameter[0].Value = taiKhoanDto.TenTaiKhoan;
                parameter[1] = new SqlParameter("@MaLoaiTK", SqlDbType.VarChar);
                parameter[1].Value = taiKhoanDto.MaLoaiTK;
                parameter[2] = new SqlParameter("@TrangThai", SqlDbType.NVarChar);
                parameter[2].Value = taiKhoanDto.TrangThai;
                parameter[3] = new SqlParameter("@DiemTinCayBan", SqlDbType.NVarChar);
                parameter[3].Value = taiKhoanDto.DiemTinCayBan;
                parameter[4] = new SqlParameter("@DiemTinCayMua", SqlDbType.NVarChar);
                parameter[4].Value = taiKhoanDto.DiemTinCayMua;
                parameter[5] = new SqlParameter("@MaTaiKhoan", SqlDbType.VarChar);
                parameter[5].Value = taiKhoanDto.MaTaiKhoan;

                foreach (SqlParameter parameters in parameter)
                {
                    sqlParameters.Add(parameters);
                }
                res = DataProvider.ExecuteNoneQuery(sql, sqlParameters);

            }
            catch (Exception ex)
            {
                res = false;
            }
            return res;
        }

        public static bool DeleteTaiKhoanofMa(string maTaiKhoan)
        {
            bool res = false;
            try
            {
                List<SqlParameter> sqlParameters = new List<SqlParameter>();
                string sql = "DELETE FROM TaiKhoan WHERE MaTaiKhoan = @MaTaiKhoan";
                SqlParameter[] parameter = new SqlParameter[1];
                parameter[0] = new SqlParameter("@MaTaiKhoan", SqlDbType.VarChar);
                parameter[0].Value = maTaiKhoan;
                sqlParameters.Add(parameter[0]);

                res = DataProvider.ExecuteNoneQuery(sql, sqlParameters);

            }
            catch (Exception ex)
            {
                res = false;
            }
            return res;
        }

        public static bool UpdateTaiKhoanOfTrangThai(string maTaiKhoan, string trangThai)
        {
            bool res = false;
            try
            {
                List<SqlParameter> sqlParameters = new List<SqlParameter>();
                string sql = "UPDATE TaiKhoan SET TrangThai = @TrangThai WHERE MaTaiKhoan = @MaTaiKhoan";
                SqlParameter[] parameter = new SqlParameter[2];
            
                parameter[0] = new SqlParameter("@TrangThai", SqlDbType.NVarChar);
                parameter[0].Value = trangThai;
                sqlParameters.Add(parameter[0]);
                parameter[1] = new SqlParameter("@MaTaiKhoan", SqlDbType.VarChar);
                parameter[1].Value = maTaiKhoan;
                sqlParameters.Add(parameter[1]);

                res = DataProvider.ExecuteNoneQuery(sql, sqlParameters);

            }
            catch (Exception ex)
            {
                res = false;
            }
            return res;
        }

        
    }
}
